package com.ebay.services;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedHashMap;

import com.ebay.model.*;
import com.ebay.util.DBconn;

public class Services {


	public static LinkedHashMap<String,String> getSubcategoryList(int category,int subcategory){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		String subCatName = null;
		String subCatLink = null;
		String query = null;
		LinkedHashMap<String,String> subCatList = new LinkedHashMap<String,String>();
		if(category!=0)
			query = "select subcategory_id,subcategory_name from subcategory where category_id = "+category;
		else
			query = "select category_id,category_name from category;";
		con = DBconn.getConnection();
		System.out.println("Query : "+query);
		try {
			if(category!=0){
				System.out.println("here in if");
				stmt = con.createStatement();
				rs = stmt.executeQuery(query);
				while(rs.next()){
					subCatName = rs.getString("subcategory_name");
					if(subcategory!=rs.getInt("subcategory_id"))
						subCatLink = "?category_id="+category+"&subcategory_id="+rs.getInt("subcategory_id");
					else
						subCatLink = "";
					subCatList.put(subCatLink,subCatName);
				}
			}else{
				System.out.println("here in else");
				stmt = con.createStatement();
				rs = stmt.executeQuery(query);
				while(rs.next()){
					subCatName = rs.getString("category_name");
					subCatLink = "?category_id="+rs.getInt("category_id");
					subCatList.put(subCatLink,subCatName);
				}
			} 
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return subCatList;
	}

	public static String getEndTimeText(Date endTime){
		Calendar d1 = Calendar.getInstance();
		Calendar d2 = Calendar.getInstance();
		long diffSeconds, diffMins, diffHours, diffDays;
		int flag = 0;
		String timeLeftString="";
		//d1.setTime(StartTime);
		d2.setTime(endTime);
		diffSeconds = (d2.getTimeInMillis()-d1.getTimeInMillis())/1000;
		diffDays = diffSeconds/(60*60*24);
		//if(diffDays>20){
		//timeLeftString = new SimpleDateFormat("d MMMM yyyy, KK:mm:ss aa").format(EndTime);
		//}else{
		diffSeconds -= diffDays * (60*60*24);
		diffHours = diffSeconds/(60*60);
		diffSeconds -= diffHours * (60*60);
		diffMins = diffSeconds/60;
		diffSeconds -= diffMins * 60;
		if(diffDays != 0){
			timeLeftString = diffDays + "d ";
			flag++;
		}
		if(diffHours != 0){
			timeLeftString += diffHours +"h ";
			flag++;
		}
		if(diffMins != 0){
			timeLeftString += diffMins +"m ";
			flag++;
		}
		if(diffSeconds != 0&&flag!=3){
			timeLeftString += diffSeconds +"s ";
		}
		return timeLeftString;
	}

	public static String getCategoryName(int category_id) {
		Connection con = DBconn.getConnection();
		Statement stmt;
		try {
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select category_name from category where category_id = "+category_id);
			if(rs.next())
				return rs.getString("category_name");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public static String getSubCategoryName(int category_id,int subcategory_id) {
		Connection con = DBconn.getConnection();
		Statement stmt;
		try {
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery("select subcategory_name from subcategory where" +
					" category_id = "+category_id+" AND subcategory_id = "+subcategory_id);
			if(rs.next())
				return rs.getString("subcategory_name");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public static int doSearch(String searchTerm, String queryFilters,
			LinkedHashMap<String, Item> itemList, int category_id,int subcategory_id,int pageNo, int itemPerPage) {
		// TODO for searching
		int returnValue = 0;
		String query = null;
		String searchFilters = "";
		String[] terms =  searchTerm.split(" ");
		ArrayList<String> tables = getAllTableNames();
		searchFilters += "title LIKE '%"+searchTerm+"%' ";
		if(terms.length>1)
			for(String term : terms)
				searchFilters += " OR title LIKE '%"+term+"%' ";
		switch (category_id) {
		case 1:
			searchFilters += " OR author LIKE '%"+searchTerm+"%' ";
			if(terms.length>1)
				for(String term : terms)
					searchFilters += " OR author LIKE '%"+term+"%' ";
			queryFilters = " AND ("+searchFilters+")" + queryFilters;
			
			break;
		case 2:
			searchFilters += " OR genre LIKE '%"+searchTerm+"%' ";
			if(terms.length>1)
				for(String term : terms)
					searchFilters += " OR genre LIKE '%"+term+"%' ";
			queryFilters = " AND ("+searchFilters+")" + queryFilters;
			//returnValue = Music.getItemList(queryFilters,itemList);
			break;
		default:
			queryFilters = " AND ("+searchFilters+")" + queryFilters;
			try {
				Statement stmt = DBconn.getConnection().createStatement();
				for(String table:tables){
					query = "select * from "+table+" where status = 1" +queryFilters;
					System.out.println(query);
					ResultSet rs = stmt.executeQuery(query);
					while(rs.next()){
						Item item = new Item(rs.getInt("category_id"), 
								rs.getInt("subcategory_id"), rs.getInt("product_id"), rs.getString("title"),
								rs.getString("description"), rs.getFloat("price"), rs.getString("cond"), 
								rs.getString("image"), rs.getInt("biddingstatus"), rs.getInt("quantity"), 
								rs.getTimestamp("expdate"), rs.getString("seller_id"), rs.getInt("status"),rs.getFloat("shipcost"));
						itemList.put(item.getDescription(), item);
						query = "SELECT FOUND_ROWS();";
						rs = stmt.executeQuery(query);
						while (rs.next()) {
							returnValue += rs.getInt(1);
						}
					}
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			if(category_id!=0)
				returnValue = Item.getItemList(queryFilters,itemList,category_id,subcategory_id);
		}

		return returnValue;
	}

	private static ArrayList<String> getAllTableNames() {
		ArrayList<String> tableNames = new ArrayList<String>();
		tableNames.add("bookdetails");
		tableNames.add("musiccd");
		tableNames.add("musiccassette");
		return tableNames;
	}

	public static void updatePrice(int category_id, int subcategory_id,
			int product_id, float bidAmount) {
		//to update table during bidding
		String query = "UPDATE ";
		switch (category_id) {
		case 1:
			query += " bookdetails ";
			break;

		default:
			break;
		}
		query += " set price = "+bidAmount+" where category_id = " +
				""+category_id+" AND subcategory_id = "+subcategory_id+" AND product_id = "+product_id+";";

		System.out.println(query);
		try {
			DBconn.getConnection().createStatement().executeUpdate(query);
		} catch (SQLException e) {

			e.printStackTrace();
		}

	}

	/*public static Item getItemDetails(int category_id,int subcategory_id,int product_id) {
		Item item = null;
		switch(category_id){
		case 1:
			item =  Book.getItemDetails(product_id);
			break;

		case 2:
			item = Music.getItemDetails(category_id,subcategory_id,product_id);
		default:
			break;
		}
		return item;
	}

	public static int getItemList(String queryFilters,
			LinkedHashMap<String, Item> itemList, int category_id,
			int subcategory_id) {
		int returnValue = 0;
		switch(category_id){
		case 1:
			//Book!
			returnValue = Book.getItemList(queryFilters,itemList,subcategory_id);
			break;
		case 2:
			//returnValue = Music.getItemList(queryFilters, itemList,subcategory_id);
		default:
			break;
		}
		return returnValue;
	}*/

	public static String getTableName(int category_id,int subcategory_id) {
		Connection con = DBconn.getConnection();
		String catName = null, subcatName = null;
		Statement stmt;
		ResultSet rs;
		String query = "";
		if(category_id==1)
			return "bookdetails";
		try {
			stmt = con.createStatement();
			query = "select category_name from category where" +
					" category_id = "+category_id+" ;";
			
			rs = stmt.executeQuery(query);
			
			if(rs.next())
				catName = rs.getString("category_name");
			
			if(subcategory_id==0)
				query = "select subcategory_name from subcategory where" +
						" category_id = "+category_id;
			
			else
				query = "select subcategory_name from subcategory where" +
						" category_id = "+category_id+" AND subcategory_id = "+subcategory_id;
			rs = stmt.executeQuery(query);
				
			if(rs.next())
				subcatName =  rs.getString("subcategory_name");
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return catName+subcatName;
	}

}
